package serverside;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {

    private static final String host = "jdbc:derby://localhost:1527/bank";
    private String user = "abdo";
    private String password = "123";
    private Connection connection;
    private Statement statement;
    ///////////////////////////////////////////////////////////////////////////

    public Database(String host_name, String user, String password)//if you want any database or user not default
    {
        try {
            //this open the connection and take host name from jdbc and user name and password
            connection = DriverManager.getConnection(host_name, user, password);
            //this create statement object and return its reference. 
            //statement object who can speak with database to do any thing through it
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    ///////////////////////////////////////////////////////////////////////

    public Database()//default database
    {
        try {
            //this open the connection and take host name from jdbc and user name and password
            connection = DriverManager.getConnection(host, user, password);
            //this create statement object and return its reference. 
            //statement object who can speak with database to do any thing through it
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /////////////////////////////////////////////////////////////////////////////////////

    protected ResultSet select_particular_client_by_IdCard(int id_card) {
        ResultSet client;
        String query = "select * from CLIENT where id_card = " + id_card;
        try {

            client = statement.executeQuery(query);
            client.next();
            //get attribute from resultset
                    /* int client_id = client.getInt("ID");
             String client_name = client.getString("NAME");
             int client_Account = client.getInt("ACCOUNT");
             System.out.println(client_id+"  "+"  "+client_name+"  "+client_Account);*/
            return client;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
//////////////////////////////////////////////////////////////////////////////////////////

    public void update_particular_info(int id_card, int account_update) {
    }

    private boolean update_Account_Money(int id_card, int money) {
        String query = "update CLIENT set money = ? where ID_CARD = " + id_card;
        try {
            PreparedStatement ptmt = null;
            ptmt = connection.prepareStatement(query);
            ptmt.setInt(1, money);

            ptmt.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("update  function");
            return false;
        }
    }
///////////////////////////////////////////////////////////////////////////////////////

    private boolean Check_IdCard(int IDCard)//check if ID_CARD exit ot not
    {
        long temp = 0;
        String sql = "select * from CLIENT";
        try {
            ResultSet re = statement.executeQuery(sql);
            while (re.next()) {
                if (re.getInt("ID_CARD") == IDCard) {
                    temp = 1;
                    break;
                }//end if checking 
            }//end while
        }//end try
        catch (SQLException err) {
            System.out.println(err.getMessage());
        }//end catch
        if (temp == 1) {

            return true;//email exist
        }//int temp=1
        else {
            return false;//email not exist
        }//end else if temp!=1
    }//IDCardchecking
///////////////////////////////////////////////////////////////////////////////////////

    private ResultSet SelectWithCondition(String condition, int value) {
        String sql = "select * from CLIENT where ";
        sql = sql + condition + " = " + value;
        ResultSet re;
        try {
            Statement stat = connection.createStatement();
            re = stat.executeQuery(sql);
            return re;
        } catch (SQLException err) {
            System.out.println(err.getMessage());

        }
        return null;

    }
    /////////////////////////////////////////////////////////////////////////////////

    public boolean login(int IDCard, int password) {
        long temp = 0;//if temp = 0 password wronge else password true
        if (Check_IdCard(IDCard) == true) {
            try {
                ResultSet re = SelectWithCondition("ID_CARD", IDCard);
                re.next();
                int pass = re.getInt("password");

                if (password == pass) {
                    temp++;
                }
            } catch (Exception e) {
                System.out.println("login fun");
            }
        } else {
            return false;
        }

        if (temp == 1) {
            return true;
        } else {
            return false;
        }
    }
////////////////////////////////////////////////////////////////////////////////

    public String Withdraw(int idCard, int money) {
        int modify_Account;
        try {
            ResultSet client = select_particular_client_by_IdCard(idCard);
            int client_Account = client.getInt("MONEY");
            if (client_Account > money) {
                modify_Account = client_Account - money;
                if (update_Account_Money(idCard, modify_Account)) {
                    return "" + modify_Account;
                } else {
                    return "0";
                }
            }//end of id Account > money
            else {
                return "0";
            }//end of else Account < money
        }//end of try
        catch (Exception e) {
            System.out.println(e.getMessage());
            return "0";
        }//end of catch

    }
////////////////////////////////////////////////////////////////////////////////

    public String Deposit(int idCard, int money) {
        int modify_Account;
        try {
            ResultSet client = select_particular_client_by_IdCard(idCard);
            int client_Account = client.getInt("MONEY");

            modify_Account = client_Account + money;
            if (update_Account_Money(idCard, modify_Account)) {
                return "" + modify_Account;
            } else {
                return "0";
            }

        } catch (Exception e) {
            System.out.println(e.getMessage());
            return "0";
        }

    }
    //////////////////////////////////////////////////////////////////////////

    private String Check_Account_nu(int Account_nu)//check if ID_CARD exit ot not
    {
        String card_id = "";
        long temp = 0;
        String sql = "select * from CLIENT";
        try {
            ResultSet re = statement.executeQuery(sql);
            while (re.next()) {
                if (re.getInt("Account_nu") == Account_nu) {
                    card_id = "" + re.getInt("id_card");
                    temp = 1;
                    break;
                }//end if checking 
            }//end while
        }//end try
        catch (SQLException err) {
            System.out.println(err.getMessage());
        }//end catch
        if (temp == 1) {

            return card_id;//email exist
        }//int temp=1
        else {
            return "false";//email not exist
        }//end else if temp!=1
    }//IDCardchecking
//0 no enough money  1  wrong Account 2-your Account money 

    public String transfer(int account_nu, int money, int card_id) {
        String card_id_res = Check_Account_nu(account_nu);
        if (!card_id_res.equals("false")) {
            //0   1 
            String re = Withdraw(card_id, money);
            if (!re.equals("0")) {
                if (Deposit(Integer.parseInt(card_id_res), money) != "0") {
                    return re;
                } else {
                    return "3";
                }
            } else {
                return "0";
            }
        } else {
            return "1";
        }
    }
    //////////////////////////////////////////////////////////////////////

    private ResultSet Select_with_string_condition(String table_name, String col_name, String value) {

        String sql = "select* from ABDO." + table_name + " where ";
        sql = sql + col_name + " = " + "'" + value + "'";
        ResultSet re;
        try {
            re = statement.executeQuery(sql);
            re.next();
            return re;
        } catch (SQLException err) {
            System.out.println(err.getMessage());

        }
        return null;
    }

    private boolean update_one_col(String table_name, String col_name, String value, String condition_col, String value_of_condition) {
        String query = "update " + table_name + " set " + col_name + " = ? where " + condition_col + " = " + value_of_condition;

        try {
            PreparedStatement ptmt = null;
            ptmt = connection.prepareStatement(query);
            ptmt.setString(1, value);
            ptmt.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("update  function");
            return false;
        }
    }

    private boolean insert_into_billbay_transaction(int Company_ID, int client_id, int phone, int money, int bill_nu) {
        String sql;
        sql = "insert into BILLBAY_TRANSACTION values(";
        sql = sql + Company_ID + "," + client_id + "," + phone + "," + money + "," + bill_nu + ")";
        try {
            PreparedStatement ptmt = null;
            ptmt = connection.prepareStatement(sql);
            ptmt.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("INSERT  function");
            return false;
        }
    }

    public String bill_payment(int card_id, String company_name, int money, int bill_nu, int phone_nu) throws SQLException {
        if (Withdraw(card_id, money) != "0") {
            ResultSet client;
            client = SelectWithCondition("ID_CARD", card_id);
            client.next();
            ResultSet company;
            company = Select_with_string_condition("COMPANY", "NAME", company_name);
            int update_company_money = company.getInt("BALABNCE");
            update_company_money = update_company_money + money;
            update_one_col("COMPANY", "BALABNCE", "" + update_company_money, "ID", "" + company.getInt("ID"));
            insert_into_billbay_transaction(company.getInt("ID"), client.getInt("CLIENT_ID"), phone_nu, money, bill_nu);
            return "" + client.getInt("MONEY");
        } else {
            return "0";
        }
    }

    public String Balancing_query(int card_id) throws SQLException {
        ResultSet re;
        re = SelectWithCondition("ID_CARD", card_id);
        re.next();
        return "" + re.getInt("MONEY");
    }
}
